using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.IO;

public partial class app_frame_Default2 : System.Web.UI.Page
{
    string strPagesetRowID = "fa94b4929612d0bb"; // ps_report_sql
    DataSet dseInputInfo = new DataSet();
    DataSet dsePageInfo = new DataSet();
    HtmlForm frmObj;
    //string strErrMessage = "";
    security hdds;

    protected void Page_PreInit(object sender, EventArgs e)
    {
        this.Theme = Session["theme"].ToString();
        this.Title = "MaxBlox";
    }

    protected void Page_Load(object sender, EventArgs e)
    {
        hdds = new security();
        
        if (Session["userid"] == null) Response.Redirect(hdds.SessionExpiryURL);
        if (Session["userid"].ToString() == "") Response.Redirect(hdds.SessionExpiryURL);

        string strErrorMsg = "";
        if (!hdds.IsValidPageURL(ref strErrorMsg))
        {
            lblMessage.Text = wrapItAsMessage(strErrorMsg, true);
            TABLE1.Visible = false;
            return;
        }

        if (Request.QueryString["mn"] != null)
            hdds.logMenuClick(Request.QueryString["mn"].ToString());

        if (this.Master.FindControl("Form1") != null)
        {
            frmObj = (HtmlForm)this.Master.FindControl("Form1");
        }
        if (!IsPostBack)
        {
            loadPrimaryObjectsCombo(); // Added by 'Imran Gani' on 01/05/12, to load primary object DropDownList.
            loadOwneridDropDown();
            if (Request.QueryString["PK"] == null)
            {
                //ScreenReader SR = new ScreenReader();
                //hf_af_row_id.Value = SR.getHashKey();
                p_sy_report_sql_report_base_id.Text = getSQLId();
            }
        }

        PageInfoLoad PIL = new PageInfoLoad();
        dsePageInfo = PIL.GetPageSetPageCtrlInfo(strPagesetRowID);
        //PageCommonFunctions PCF = new PageCommonFunctions();
        //PCF.AddSearchLink(ref tdownerid, "owner_id", "p_sy_report_sql_owner_id","txt_p_sy_report_sql_owner_id", "92");

        CheckForChangeOwner();
        checkEditOperation();
        p_sy_report_sql_report_base_name.Focus();
        if (!Page.IsPostBack)
        {
            getpageicons(); // Added by srivatsan
            
        }
        AddPageToolTip(dsePageInfo.Tables["Page"].Rows[0], ref lblHeader);
        isViewMode();
    }
    private void isViewMode()
    {
        if (HttpContext.Current.Request.QueryString["VMODE"] != null)
            if (HttpContext.Current.Request.QueryString["VMODE"] == "1")
            {
                DynamicDisplayI ddi = new DynamicDisplayI();
                ddi.disableControls(this);
                ddi = null;
            }
    }
    private void loadPrimaryObjectsCombo()
    {
        HDDatabase hd = new HDDatabase();
        DataTable dt = new DataTable();
        try
        {
            string Sql = "Select * from sy_obj";
            Sql = applysecurity(Sql);
            dt = hd.GetDataTable(Sql);

            p_sy_report_sql_primary_object_id.Items.Clear();
            p_sy_report_sql_primary_object_id.Items.Add(new ListItem("--Select a Object--", ""));

            foreach (DataRow dr in dt.Select("", "object_name asc"))
            {
                if (hdds.getObjectRightsForUser(dr["object_id"].ToString(), "read"))
                {
                    ListItem liObject = new ListItem(dr["object_name"].ToString(), dr["af_row_id"].ToString());
                    p_sy_report_sql_primary_object_id.Items.Add(liObject);
                }
            }
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("loadPrimaryObjectsCombo : Error : " + ex.Message);
        }
        finally
        {
            dt.Dispose();
        }
    }
    private string applysecurity(string sql)
    {
        try
        {
            SecurityFilter objSec = new SecurityFilter();
            string sAFAdmin = objSec.QueryForAFAdmin();
            string sCustodianFilter = objSec.QueryForCustodianFilter();
            if (sCustodianFilter.Trim() != "")
                if (!sCustodianFilter.StartsWith("or"))
                    sAFAdmin += " and " + sCustodianFilter;
                else
                    sAFAdmin = "(" + sAFAdmin + " " + sCustodianFilter + ") ";

            if (sql.Contains("order by"))
            {
                if (sAFAdmin.Trim() != "")
                    sql = sql.Replace("order by", "where " + sAFAdmin + " order by");
            }
            else
            {
                if (sAFAdmin.Trim() != "")
                    sql = sql + " where " + sAFAdmin;
            }
        }
        catch
        {

        }
        return sql;
    }
    private string wrapItAsMessage(string msg, bool isError)
    {
        return "<div class=\"" + (isError == true ? "mbErrorMessage" : "mbMessage") + "\">" + msg + "</div>";
    }
    protected void btnSave_Click(object sender, EventArgs e)
    {
        string sReturn = validateSQL();

        if (sReturn == "")
        {
            if (checkSQL())
            {
                if (SaveData())
                {
                    string refURL = getRefURL();
                    if (refURL.Length > 0)
                        Response.Redirect(refURL);
                }
            }
        }
        else
            lblMessage.Text = wrapItAsMessage(sReturn,true);
    }

    protected void btnUpdate_Click(object sender, EventArgs e)
    {
        string sReturn = validateSQL();

        if (sReturn == "")
        {
            if (checkSQL())
            {
                if (UpdateData())
                {

                    string refURL = getRefURL();
                    if (refURL.Length > 0)
                        Response.Redirect(refURL);
                }
            }
        }
        else
            lblMessage.Text = wrapItAsMessage(sReturn,true);
    }

    private bool SaveData()
    {
        bool blnStatus = true;
        ScreenReader SR = new ScreenReader();
        dseInputInfo = SR.CaptureData(strPagesetRowID, frmObj, dsePageInfo,true);
        if (SR.errormessage != "")
        {
            lblMessage.Text = wrapItAsMessage("<img src=\"Images/error.gif\" alt=\"Error\"></img>&nbsp;" + "Data Insert not successful. Error: " + HttpUtility.HtmlEncode(SR.errormessage),true);;
            return false;
        }
        //function added by Sampath on 17-Nov-2011 for IPP Tech check 
        //commented by Sampath on 27-Apr-2012  for double time calling encode data
        //CommonFunctions.encodeData(dseInputInfo);
        Master MA = new Master();
        blnStatus = MA.ProcessOperation(dsePageInfo, dseInputInfo, "INSERT");
        if (blnStatus == false)
            lblMessage.Text = wrapItAsMessage("<img src=\"Images/error.gif\" alt=\"Error\"></img>&nbsp;" + "Data Insert not successful. Error: " + HttpUtility.HtmlEncode(MA.ErrorMessage),true);
        else
            lblMessage.Text = wrapItAsMessage("&nbsp;<img src='Images/info.gif' border='0'>&nbsp;Data Inserted Successfully.",false);//changed by prithi on 07-Aug-2008
        return blnStatus;
    }

    private string getRefURL()
    {
        try
        {
            return "DisplayGrid.aspx?pgs_id=" + Request.QueryString["pgs_id"].ToString() + "&mn=" + Request.QueryString["mn"].ToString();
        }
        catch
        {
            return "";
        }
    }

    private string getSQLId()
    {
        string sSQLId = "1";
        HDDatabase hdd = new HDDatabase();
        try
        {
            string sTemp = hdd.GetColumnValue("Select max(convert(int,report_base_id)) from sy_report_sql");
            if (sTemp != "")
                sSQLId = Convert.ToString(Convert.ToInt32(sTemp) + 1);
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("getSQLId : Error : " + ex.Message);
            return "1";
        }
        return sSQLId;
    }

    private void disableControls()
    {
        p_sy_report_sql_report_base_name.Enabled = false;
        p_sy_report_sql_report_base_desc.Enabled = false;
        p_sy_report_sql_report_base_sql.Enabled = false;
        btnSave.Enabled = false;
        btnUpdate.Enabled = false;
    }

    private bool getData()
    {
        bool bReturn = true;
        try
        {
            HDDatabase hdd = new HDDatabase();
            
            string sPageKey = Request.QueryString["PK"].ToString();
            p_sy_report_sql_af_row_id.Value = sPageKey;
            sPageKey = hdd.GetColumnValue("select report_base_id from sy_report_sql where af_row_id = '" + sPageKey + "'");
            foreach (DataRow dr in dsePageInfo.Tables["page"].Rows)
            {
                string sObject_id = dr["object_id"].ToString();
                if (hdds.getObjectRightsForUser(dr["object_id"].ToString(), "read"))
                {
                    string sSql = "select * from " + dr["page_table_name"].ToString() + " where report_base_id = '" + sPageKey + "'";
                    DataTable dt = hdd.GetDataTable(sSql);
                    dt.TableName = dr["page_id"].ToString();
                    dseInputInfo.Tables.Add(dt.Copy());

                    //function added by Sampath on 17-Nov-2011 for IPP Tech check 
                    //CommonFunctions.decodeData(dseInputInfo);
                }
                else
                {
                    disableControls();
                    bReturn = false;
                }
            }
        }

        catch (Exception ex)
        {
            LogWriter.WriteLog("Error : " + ex.Message);
            return false;
        }
        return bReturn;
    }

    private void setData(bool IsEdit)
    {

        //function added by bala on 18-Nov-2011 for IPP Tech check 
        //CommonFunctions.decodeData(dseInputInfo);
        if (dseInputInfo.Tables["p_sy_report_sql"] != null)
        {
            if (dseInputInfo.Tables["p_sy_report_sql"].Rows[0] != null)
            {                
                if (IsEdit)
                {

                    if (Request.QueryString["CPY"] != null)
                    {
                        ScreenReader SR = new ScreenReader();
                        p_sy_report_sql_af_row_id.Value = SR.getHashKey();
                        SR = null;
                        p_sy_report_sql_owner_id_dd.SelectedValue = Session["user_afrowid"].ToString();
                        p_sy_report_sql_owner_id.Value = Session["user_afrowid"].ToString();
                        txt_p_sy_report_sql_owner_id.Text = Session["userid"].ToString();
                    }
                    else
                    {
                        p_sy_report_sql_af_row_id.Value = dseInputInfo.Tables["p_sy_report_sql"].Rows[0]["af_row_id"].ToString();
                        p_sy_report_sql_owner_id_dd.SelectedValue = dseInputInfo.Tables["p_sy_report_sql"].Rows[0]["owner_id"].ToString();
                        p_sy_report_sql_owner_id.Value = dseInputInfo.Tables["p_sy_report_sql"].Rows[0]["owner_id"].ToString();
                        LoadEditPage LEP = new LoadEditPage();
                        txt_p_sy_report_sql_owner_id.Text = LEP.GetUserIDFromAfrowid(dseInputInfo.Tables["p_sy_report_sql"].Rows[0]["owner_id"].ToString());
                        LEP = null;

                    }

                    p_sy_report_sql_report_base_id.Text = dseInputInfo.Tables["p_sy_report_sql"].Rows[0]["report_base_id"].ToString();
                    p_sy_report_sql_created_at.Value = dseInputInfo.Tables["p_sy_report_sql"].Rows[0]["created_at"].ToString();
                    p_sy_report_sql_created_by.Value = dseInputInfo.Tables["p_sy_report_sql"].Rows[0]["created_by"].ToString();
                    // Added by srivatsan on June 15 2009 for owner id field
                    
                }
                else
                {
                    ScreenReader sc = new ScreenReader();
                    p_sy_report_sql_af_row_id.Value = sc.getHashKey();
                    p_sy_report_sql_report_base_id.Text = getSQLId();
                    p_sy_report_sql_owner_id_dd.SelectedValue = Session["userafrowid"].ToString();
                    p_sy_report_sql_owner_id.Value = Session["user_afrowid"].ToString();
                    txt_p_sy_report_sql_owner_id.Text = Session["userid"].ToString();
                }

                p_sy_report_sql_report_base_name.Text = dseInputInfo.Tables["p_sy_report_sql"].Rows[0]["report_base_name"].ToString();
                p_sy_report_sql_report_base_desc.Text = dseInputInfo.Tables["p_sy_report_sql"].Rows[0]["report_base_desc"].ToString();
                p_sy_report_sql_report_base_sql.Text = dseInputInfo.Tables["p_sy_report_sql"].Rows[0]["report_base_sql"].ToString();
                p_sy_report_sql_report_base_param.Text = dseInputInfo.Tables["p_sy_report_sql"].Rows[0]["report_base_param"].ToString();
                // Below line added by 'Imran Gani' on 02/05/12, to get the value for primary object.
                p_sy_report_sql_primary_object_id.SelectedValue = dseInputInfo.Tables["p_sy_report_sql"].Rows[0]["primary_object_id"].ToString();
            }
        }
    }

    private bool checkEditOperation()
    {
        bool bEdit = false;
        if (!IsPostBack)
        {
            if (Request.QueryString["PK"] != null)
            {
                lblHeader.Text = "Query for Report - Edit";
                bEdit = true;
                if (getData())
                    setData(bEdit);
            }

            if (Request.QueryString["CPY"] != null)
            {
                if (Request.QueryString["CPY"] == "0")
                {
                    lblHeader.Text = "Query for Report - Copy";
                    bEdit = false;
                }
            }

            if (bEdit)
            {
                btnSave.Visible = false;
                btnUpdate.Visible = true;
            }
            else
            {
                btnUpdate.Visible = false;
                btnSave.Visible = true;
            }
        }
        return true;
    }

    private bool UpdateData()
    {
        bool blnStatus = true;
        ScreenReader SR = new ScreenReader();
        dseInputInfo = SR.CaptureData(strPagesetRowID, frmObj, dsePageInfo,false);
        if (SR.errormessage != "")
        {
            lblMessage.Text = wrapItAsMessage("<img src=\"Images/error.gif\" alt=\"Error\"></img>&nbsp;" + "Data Update not successful. Error: " + HttpUtility.HtmlEncode(SR.errormessage),true);
            return false;
        }


        Master MA = new Master();
        blnStatus = MA.ProcessOperation(dsePageInfo, dseInputInfo, "UPDATE");
        if (blnStatus == false)
            lblMessage.Text = wrapItAsMessage("<img src=\"Images/error.gif\" alt=\"Error\"></img>&nbsp;" + "Data Update not successful. Error: " + HttpUtility.HtmlEncode(MA.ErrorMessage),true);
        else
            lblMessage.Text = wrapItAsMessage("&nbsp;<img src='Images/info.gif' border='0'>&nbsp;Data Updated Successfully.",false);//changed by prithi on 07-Aug-2008
        return blnStatus;
    }
    protected void btnReset_Click(object sender, EventArgs e)
    {
        string strPagesetRowID = Request.QueryString["pgs_id"].ToString();
        string strMenuRowId = Request.QueryString["mn"].ToString();
        //added for static page list view AddNew
        HDDatabase HDD = new HDDatabase();
        GenerateGrid objGD = new GenerateGrid();
        string sEditPage = objGD.getEditPage(HDD, strMenuRowId);
        //
        string strPageUrl = sEditPage + "?pgs_id=" + strPagesetRowID + "&mn=" + strMenuRowId;
        Response.Redirect(strPageUrl);
    }

    private string validateSQL()
    {
        string sReturn = "";
        try
        {

            if (p_sy_report_sql_report_base_sql.Text.Trim() == "")
                sReturn = "<img src=\"Images/error.gif\" alt=\"Error\"></img>&nbsp;Please input a query.";
            else if (!p_sy_report_sql_report_base_sql.Text.Trim().ToLower().Contains("select"))
                sReturn = "<img src=\"Images/error.gif\" alt=\"Error\"></img>&nbsp;The SQL should be a select statement.";
            else if (p_sy_report_sql_report_base_sql.Text.Trim().ToLower().StartsWith("insert"))
                sReturn = "<img src=\"Images/error.gif\" alt=\"Error\"></img>&nbsp;insert statements are not allowed.";
            else if (p_sy_report_sql_report_base_sql.Text.Trim().ToLower().StartsWith("update"))
                sReturn = "<img src=\"Images/error.gif\" alt=\"Error\"></img>&nbsp;update statements are not allowed.";
            else if (p_sy_report_sql_report_base_sql.Text.Trim().ToLower().StartsWith("delete"))
                sReturn = "<img src=\"Images/error.gif\" alt=\"Error\"></img>&nbsp;delete statements are not allowed.";
            else if (p_sy_report_sql_report_base_sql.Text.Trim().ToLower().StartsWith("truncate"))
                sReturn = "<img src=\"Images/error.gif\" alt=\"Error\"></img>&nbsp;truncate statements are not allowed.";
            else if (p_sy_report_sql_report_base_sql.Text.Trim().ToLower().StartsWith("alter"))
                sReturn = "<img src=\"Images/error.gif\" alt=\"Error\"></img>&nbsp;alter statements are not allowed.";
            else if (p_sy_report_sql_report_base_sql.Text.Trim().ToLower().StartsWith("drop"))
                sReturn = "<img src=\"Images/error.gif\" alt=\"Error\"></img>&nbsp;drop statements are not allowed.";
            else if (p_sy_report_sql_report_base_param.Text.Trim() != "")
            {
                string[] tmp = p_sy_report_sql_report_base_param.Text.ToLower().Replace("parameters", "").Trim().Split(',');
                foreach (string st in tmp)
                {
                    if (st.Trim() == "") continue;
                    if (st.IndexOf(".") == -1)
                    {
                        sReturn = "<img src=\"Images/error.gif\" alt=\"Error\"></img>&nbsp;Parameters not defined properly.";
                        break;
                    }
                    string parName = st.Substring(0, st.IndexOf("]") + 1).Trim();
                    string parName1 = parName;
                    if (!parName.StartsWith("[") || !parName.EndsWith("]"))
                    {
                        sReturn = "<img src=\"Images/error.gif\" alt=\"Error\"></img>&nbsp;Parameters not defined properly.";
                        break;
                    }
                    string[] dots = st.Replace(parName, "").Split('.');
                    parName = parName.Replace("[", "$").Replace("]", "$");
                    parName1 = parName1.Replace("[", "#").Replace("]", "#");
                    if (dots.Length < 2)
                    {
                        sReturn = "<img src=\"Images/error.gif\" alt=\"Error\"></img>&nbsp;Parameters not defined properly.";
                        break;
                    }
                    if (!(dots[0].Trim().StartsWith("[") && dots[1].Trim().Contains("]")))
                    {
                        sReturn = "<img src=\"Images/error.gif\" alt=\"Error\"></img>&nbsp;Parameters not defined properly.";
                        break;
                    }
                    if ((dots[0].Trim().EndsWith("]") && dots[1].Trim().StartsWith("[")))
                    {
                        sReturn = "<img src=\"Images/error.gif\" alt=\"Error\"></img>&nbsp;Parameters not defined properly.";
                        break;
                    }
                    if (p_sy_report_sql_report_base_sql.Text.ToLower().IndexOf(parName.ToLower()) == -1)
                    {
                        if (p_sy_report_sql_report_base_sql.Text.ToLower().IndexOf(parName1.ToLower()) == -1)
                        {
                            sReturn = "<img src=\"Images/error.gif\" alt=\"Error\"></img>&nbsp;Parameters not used in the SQL Query.";
                            break;
                        }
                    }
                }
            }
            else
            {
                if (p_sy_report_sql_report_base_sql.Text.ToLower().Contains("from"))
                {
                    #region MyRegion
                    //int iStartPos = 0;
                    //int iEndPos = 0;

                    //iStartPos = TextBox1.Text.Trim().IndexOf(" from ") + " from ".Length;
                    //iEndPos = TextBox1.Text.Trim().IndexOf(" ", iStartPos);

                    //if (iEndPos == -1)
                    //    iEndPos = TextBox1.Text.Trim().Length;

                    //string sObject_Name = TextBox1.Text.Trim().Substring(iStartPos, iEndPos - iStartPos); 
                    #endregion
                }
                else
                    sReturn = "<img src=\"Images/error.gif\" alt=\"Error\"></img>&nbsp;The query contains no 'from' clause. Please check the syntax.";
            }
        }

        catch (Exception ex)
        {
            return ex.Message;
        }

        return sReturn;
    }

    private bool checkSQL()
    {
        SqlConnection objConn = null;
        SqlCommand objCmd = null;

        if (!p_sy_report_sql_report_base_param.Text.Trim().ToLower().StartsWith("parameters") && p_sy_report_sql_report_base_param.Text.Trim() != "")
            p_sy_report_sql_report_base_param.Text = "parameters " + p_sy_report_sql_report_base_param.Text;

        string query = p_sy_report_sql_report_base_sql.Text;//p_sy_report_sql_report_base_param.Text.Trim() + ";" + 

        try
        {
            //No $,#,PARAMETERS - regular query
            //modified by Sampath on 03/02/10
            if (!query.Contains("$") && !query.Contains("#") && p_sy_report_sql_report_base_param.Text.Trim() == "")
            {
                lblMessage.Text = "";
                objConn = new SqlConnection(Session["dbcon"].ToString());
                objConn.Open();
                objCmd = new SqlCommand(query, objConn);
                int iResult = objCmd.ExecuteNonQuery();
                objConn.Close();
                objCmd.Dispose();
                objConn.Dispose();
                objCmd = null;
                objConn = null;
            }
            //$,# - No PARAMETERS - parameterized query with old design
            else if ((query.Contains("$") && query.Contains("#")) && p_sy_report_sql_report_base_param.Text.Trim() == "")
            {
                if (!checkVariables("$")) return false;
                if (!checkVariables("#")) return false;
                Hashtable hsParam = new Hashtable();
                getParameters("$", ref hsParam);
                getParameters("#", ref hsParam);
                string sSql = query;

                foreach (string sParam in hsParam.Keys)
                    sSql = sSql.Replace(sParam, "");

                objConn = new SqlConnection(Session["dbcon"].ToString());
                objConn.Open();
                objCmd = new SqlCommand(sSql, objConn);
                int iResult = objCmd.ExecuteNonQuery();
                objConn.Close();
                objCmd.Dispose();
                objConn.Dispose();
                objCmd = null;
                objConn = null;
                hsParam = null;
            }
            //PARAMETERS - no $,# - parameterized query with new design
            //added by Sampath on 03/02/10
            else if (p_sy_report_sql_report_base_param.Text.Trim() != "")
            {
                //Parameter and SQL Query separator is ;
                //if (!checkVariables(";")) return false;
                Hashtable hsParam = new Hashtable();
                string param = p_sy_report_sql_report_base_param.Text;
                //get the variables list from p_sy_report_sql_report_base_param
                if (p_sy_report_sql_report_base_param.Text.ToLower().StartsWith("parameters"))
                    param = p_sy_report_sql_report_base_param.Text.Replace("parameters", "");
                foreach (string str1 in param.Split(','))
                {
                    if (str1.Trim() == "") continue;
                    if (str1.IndexOf("=") == -1)
                        hsParam.Add(str1.Trim(), getDefaultValue(str1.Substring(str1.IndexOf("]") + 1).Trim()));
                    else
                        hsParam.Add(str1.Substring(0, str1.IndexOf("=")).Trim(), str1.Substring(str1.IndexOf("=") + 1).Trim());
                }

                ReplaceSysVariables(ref hsParam); // Added by 'Imran Gani' on 09-Jul-2012, to replace the system variables with their values.

                foreach (string sParam in hsParam.Keys)
                {
                    string tmpP = "$" + sParam.Substring(0, sParam.LastIndexOf("[")).Replace("[", "").Replace("]", "") + "$";
                    string outVal = checkSingleQuote(tmpP, hsParam[sParam].ToString(), query);
                    query = query.Replace(tmpP, outVal);//hsParamNew[sParam].ToString()

                    tmpP = "#" + sParam.Substring(0, sParam.LastIndexOf("[")).Replace("[", "").Replace("]", "") + "#";
                    outVal = checkSingleQuote(tmpP, hsParam[sParam].ToString(), query);
                    query = query.Replace(tmpP, outVal);//hsParamNew[sParam].ToString()
                }

                objConn = new SqlConnection(Session["dbcon"].ToString());
                objConn.Open();
                objCmd = new SqlCommand(query, objConn);
                int iResult = objCmd.ExecuteNonQuery();
                objConn.Close();
                objCmd.Dispose();
                objConn.Dispose();
                objCmd = null;
                objConn = null;
                hsParam = null;
            }
        }
        catch (SqlException ex)
        {
            lblMessage.Text = wrapItAsMessage(ex.Message,true);
            return false;
        }

        catch (Exception ex)
        {
            lblMessage.Text = wrapItAsMessage(ex.Message,true);
            return false;
        }
        finally
        {
            if (objConn != null)
            {
                objConn.Close();
                objConn.Dispose();
                objCmd = null;
            }

            if (objCmd != null)
            {
                objCmd.Dispose();
                objConn = null;
            }
        }
        return true;
    }

    private string checkSingleQuote(string parName, string inputVal, string qry)
    {
        string outVal = inputVal;
        try
        {
            if (qry.IndexOf(parName) != -1)
            {
                string tmp = qry.Substring(qry.IndexOf(parName) - 3, 3 + parName.Length);
                if (tmp.Contains("%") || tmp.Contains("'"))
                    outVal = inputVal.TrimStart('\'').TrimEnd('\'');
            }
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("checkSingleQuote:Error:" + ex.Message);
        }
        return outVal;
    }

    private string getDefaultValue(string object_field_id)
    {
        string retValue = "''";
        try
        {
            string object_table = object_field_id.Replace("[", "").Substring(0, object_field_id.Replace("[", "").IndexOf("."));
            string field_id = object_field_id.Substring(object_field_id.IndexOf(".") + 1).Replace("]", "");

            HDDatabase hdd = new HDDatabase();
            DataTable dt = hdd.GetDataTable("select control_attrib_type from sy_pg_ctr where control_id='" + field_id + "' and page_id=(select af_row_id from sy_pg where page_table_name='" + object_table + "')");
            if (dt.Rows.Count > 0)
            {
                if (dt.Rows[0]["control_attrib_type"].ToString().ToUpper() == "TX")
                    retValue = "''";
                else if (dt.Rows[0]["control_attrib_type"].ToString().ToUpper() == "DA" || dt.Rows[0]["control_attrib_type"].ToString().ToUpper() == "DT")
                    retValue = "'" + DateTime.Now.ToShortDateString() + "'";
                else
                    retValue = "0";
            }
            dt = null;
            hdd = null;
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("getDefaultValue : Error : " + ex.Message);
        }
        return retValue;
    }

    private Hashtable getParameters(string sChar, ref Hashtable hsParam)
    {
        try
        {
                string sSql = p_sy_report_sql_report_base_sql.Text.Trim();
                int iPos = 0;

                while (iPos < sSql.Length & iPos != -1)
                {
                    string sParam = "";
                    iPos = sSql.IndexOf(sChar, iPos);
                    if (iPos != -1)
                    {
                        int iEndPos = sSql.IndexOf(sChar, iPos + 1);
                        if (iEndPos == -1)
                            iEndPos = sSql.Length - iPos;
                        else
                            iEndPos = iEndPos - (iPos - 1);
                        sParam = sSql.Substring(iPos, iEndPos);
                        sParam = sParam.TrimEnd('\'');
                        if (!hsParam.Contains(sParam))
                            hsParam.Add(sParam, "");
                        iPos = iPos + iEndPos;
                    }
                }
        }

        catch (Exception Ex)
        {
            LogWriter.WriteLog("getParameters : Error : " + Ex.Message);
        }

        return hsParam;
    }


    private bool checkVariables(string sChar)
    {
        try
        {
            int iPos = 0;
            string sSql = p_sy_report_sql_report_base_sql.Text;
            while (iPos < sSql.Length & iPos != -1)
            {
                iPos = sSql.IndexOf(sChar, iPos);
                if (iPos != -1)
                {
                    int iEndPos = sSql.IndexOf(sChar, iPos + 1);
                    if (iEndPos < iPos)
                    {
                        lblMessage.Text = wrapItAsMessage("<img src=\"Images/error.gif\" alt=\"Error\"></img>&nbsp;Please check the syntax",true);
                        return false;
                    }
                    iPos = iEndPos + 1;
                }
            }
        }

        catch (Exception Ex)
        {
            LogWriter.WriteLog("checkVariables : Error : " + Ex.Message);
            return false;
        }
        return true;
    }

    protected void btnCheck_Click(object sender, EventArgs e)
    {
        string sReturn = validateSQL();

        if (sReturn == "")
        {
            if (checkSQL())
                lblMessage.Text = wrapItAsMessage("&nbsp;<img src='Images/tick4.gif' border='0'>&nbsp;Command(s) completed successfully.",false);//changed by prithi on 07-Aug-2008
        }
        else
            lblMessage.Text = wrapItAsMessage(sReturn,true);
    }

    private void getpageicons()// Added by srivatsan to get page icon
    {
        PageIcons icons = new PageIcons();
        HDDatabase Hdd = new HDDatabase();
        try
        {
            string strPagesetRowID = Request.QueryString["pgs_id"].ToString();

            lblHeader.Text = "&nbsp;" + icons.geticontextfrompageset(strPagesetRowID, 2) + "&nbsp;" + lblHeader.Text;
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("Error :getpageicons() " + ex.Message);
            lblHeader.Text = "<img src=" + PageIcons.strdefaulticon.ToString() + "></img>&nbsp;" + lblHeader.Text;
        }
        finally
        {
            icons = null;
            Hdd = null;
        }

    }

    //private void loadOwneridDropDown() // Added by srivatsan for owner id change
    //{
    //    HDDatabase Hdd = new HDDatabase();
    //    BindControl BC = new BindControl();
    //    //DataSet ds = new DataSet();
    //    DataTable dt = null;
    //    try
    //    {
    //        string bindid = Hdd.GetColumnValue("select mem_value3 from sy_acr_mem where acr_id = 'cee69885ce44589c' and mem_id = 'owner_id'");
    //        BC.Bind(bindid, p_sy_report_sql_owner_id_dd,ref dt );

    //        if (Session["user_afrowid"].ToString() == "e51e0327224a2eb9")
    //        {
    //            p_sy_report_sql_owner_id_dd.Items.Add(new ListItem(Session["userid"].ToString(), Session["user_afrowid"].ToString()));
    //        }

    //        if (Request.QueryString["PK"] == null)
    //        {
    //            p_sy_report_sql_owner_id_dd.SelectedValue = Session["user_afrowid"].ToString();
    //        }
    //        if (Request.QueryString["PK"] != null)
    //        {
    //            if (Request.QueryString["CPY"] != null)
    //            {
    //                p_sy_report_sql_owner_id_dd.SelectedValue = Session["user_afrowid"].ToString();
    //            }

    //        }
    //        //p_sy_report_sql_owner_id.Items.Add(new ListItem(Session["user_afrowid"].ToString(), Session["user_afrowid"].ToString()));

    //    }
    //    catch (Exception ex)
    //    {
    //        LogWriter.WriteLog("Error : loadOwneridDropDown :" + ex.Message);
    //    }
    //    finally
    //    {
    //        Hdd = null;
    //        BC = null;
    //        dt = null;
    //    }


    //}

    private void loadOwneridDropDown() // Added by srivatsan for owner id change
    {
        HDDatabase Hdd = new HDDatabase();
        BindControl BC = new BindControl();
        //DataSet ds = new DataSet();
        DataTable dt = null;
        try
        {
           // string bindid = Hdd.GetColumnValue("select mem_value3 from sy_acr_mem where acr_id = 'cee69885ce44589c' and mem_id = 'owner_id'");
            //BC.Bind(bindid, p_sy_report_sql_owner_id_dd, ref dt);

            //if (Session["user_afrowid"].ToString() == "e51e0327224a2eb9")
            //{
            //    p_sy_report_sql_owner_id_dd.Items.Add(new ListItem(Session["userid"].ToString(), Session["user_afrowid"].ToString()));
            //}

            if (Request.QueryString["PK"] == null)
            {
                //p_sy_report_sql_owner_id_dd.SelectedValue = Session["user_afrowid"].ToString();
                p_sy_report_sql_owner_id.Value = Session["user_afrowid"].ToString();
                txt_p_sy_report_sql_owner_id.Text = Session["userid"].ToString();

            }
            if (Request.QueryString["PK"] != null)
            {
                if (Request.QueryString["CPY"] != null)
                {
                    p_sy_report_sql_owner_id.Value = Session["user_afrowid"].ToString();
                    txt_p_sy_report_sql_owner_id.Text = Session["userid"].ToString();
                }

            }
            //p_sy_report_sql_owner_id.Items.Add(new ListItem(Session["user_afrowid"].ToString(), Session["user_afrowid"].ToString()));

        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("Error : loadOwneridDropDown :" + ex.Message);
        }
        finally
        {
            Hdd = null;
            BC = null;
            dt = null;
        }


    }

    private void CheckForChangeOwner()  // Added by srivatsan to Check if user can change owner
    {
        try
        {
            security sec = new security();
            bool status = false;
            status = sec.getRightsForMRUObjects(HttpContext.Current.Request.QueryString["pgs_id"].ToString(), "change_owner");
            if (status == false)
            {

                txt_p_sy_report_sql_owner_id.Enabled = false;

            }
            else
            {
                PageCommonFunctions PCF = new PageCommonFunctions();
                PCF.AddSearchLink(ref tdownerid, "owner_id", "p_sy_report_sql_owner_id", "txt_p_sy_report_sql_owner_id", "f496930dfe0b690f");
                PCF = null;
            }

            sec = null;
        }
        catch
        {

        }
    }

    private void AddPageToolTip(DataRow drpage, ref Label lblHeader)
    {
        if (drpage != null)
        {
            if(!Page.IsPostBack)
                lblHeader.Text = lblHeader.Text + getTootipIcon(drpage);
            Literal lit = new Literal();
            lit.Text = getTooltipContent(drpage);
            td_Page_ToopTip.Controls.Add(lit);
        }
    }
    private string getTootipIcon(DataRow droPage)
    {
        try
        {
            if (!droPage.Table.Columns.Contains("page_desc")) return "";
            if (droPage["page_desc"].ToString().Trim() == "") return "";
            return "&nbsp;<img id=\"page_tooltip_img\" style=\"cursor:pointer;\" src=\"Images/collapse3.gif\" onClick=\"javascript:showHideTooltipDIV();\" border=\"0\"></img>";
        }
        catch (Exception e)
        {
            return "";
        }
    }

    private string getTooltipContent(DataRow droPage)
    {
        if (!droPage.Table.Columns.Contains("page_desc")) return "";
        if (droPage["page_desc"].ToString().Trim() == "") return "";
        return getContent(HttpContext.Current.Server.MapPath("") + @"\scripts\pagetooltipcontent.txt").Replace("<<content>>", CommonFunctions.SafeEncode(droPage["page_desc"].ToString()));
    }

    private string getContent(string fileName)
    {
        try
        {
            if (!File.Exists(fileName))
                return "";
            string temp = "";
            StreamReader sr = new StreamReader(fileName);
            temp = sr.ReadToEnd();
            sr.Close();
            sr = null;
            return temp;
        }
        catch (Exception e)
        {
            return "";
        }
    }

    /// <summary>
    /// To replace the system variables with their values
    /// </summary>
    /// <param name="hsParam">Parameter HashTable</param>
    private void ReplaceSysVariables(ref Hashtable hsParam) // Implemented by 'Imran Gani' on 09-Jul-2012
    {
        try
        {
            ArrayList keys = new ArrayList();
            foreach (string de in hsParam.Keys)
                keys.Add(de);

            foreach (string strParam in keys)
            {
                if (hsParam[strParam].ToString().StartsWith("{!") && hsParam[strParam].ToString().EndsWith("}"))
                {
                    Hashtable hsSysVar = new Hashtable();
                    if (HttpContext.Current.Session["sysvar"] == null)
                    {
                        loadSystemVariables(ref hsSysVar);
                        HttpContext.Current.Session["sysvar"] = hsSysVar;
                    }
                    else
                        hsSysVar = (Hashtable)HttpContext.Current.Session["SysVar"];
                    if (hsSysVar.Contains(hsParam[strParam].ToString()))
                        hsParam[strParam] = hsSysVar[hsParam[strParam].ToString()].ToString();
                }
                else if (hsParam[strParam].ToString().StartsWith("{@") && hsParam[strParam].ToString().EndsWith("}"))
                {
                    security hds = new security();
                    Hashtable hsAppVar = new Hashtable();
                    hds.loadAppVariables(ref hsAppVar);
                    hds = null;
                    if (hsAppVar.Contains(hsParam[strParam].ToString()))
                        hsParam[strParam] = hsAppVar[hsParam[strParam].ToString()].ToString();
                }
                else if (hsParam[strParam].ToString() == "CurrentDate()")
                {
                    hsParam[strParam] = DateTime.Now.ToShortDateString();
                }
            }
        }
        catch (Exception e)
        {
            LogWriter.WriteLog("Error in ReportSQL:ReplaceSysVariables: " + e.Message);
        }
        finally
        {
        }
    }

    /// <summary>
    /// To load the system variables.
    /// </summary>
    /// <param name="sysVar">HashTable for system variables</param>
    private void loadSystemVariables(ref Hashtable sysVar) // Implemented by 'Imran Gani' on 09-Jul-2012
    {
        Master objmaster = new Master();
        try
        {
            DataSet ds = new DataSet();
            string dsTemp = "";
            HDDatabase db = new HDDatabase();
            ds = db.GetDataset("select * from sy_var");
            if (ds.Tables.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    switch (dr["sy_var_type"].ToString())
                    {
                        case "DQ"://database query
                            dsTemp = db.GetColumnValue(objmaster.ReplaceVariables(dr["sy_var_value"].ToString(), ref sysVar));

                            if (sysVar.Contains("{!" + dr["sy_var_name"].ToString() + "}"))
                            {
                                if (dsTemp.ToLower() != sysVar["{!" + dr["sy_var_name"].ToString() + "}"].ToString().Trim())
                                {
                                    if (dsTemp.ToString().Trim() != "")
                                    {
                                        sysVar["{!" + dr["sy_var_name"].ToString() + "}"] = dsTemp;
                                    }
                                }
                            }
                            else
                                sysVar.Add("{!" + dr["sy_var_name"].ToString() + "}", dsTemp);
                            break;
                        case "CO"://constant
                            if (dr["sy_var_value"].ToString().Length > 0)
                                sysVar.Add("{!" + dr["sy_var_name"].ToString() + "}", dr["sy_var_value"]);
                            else
                                sysVar.Add("{!" + dr["sy_var_name"].ToString() + "}", dr["sy_var_defaultvalue"]);
                            break;
                    }
                }
            }
            ds = null;
            db = null;
        }
        catch (Exception e)
        {
            LogWriter.WriteLog("Error in ReportSQL:loadSystemVariables: " + e.Message);
        }
        finally
        {
            objmaster = null;
        }
    }
    
}
